*** NOTE: FARM CAPITAL DATA FOR TEXAS NOT AVAIALBLE IN 1930, STARTS IN 1933

clear all
set more off
cd "/Users/yasenov/Dropbox (IPL)/mexican_repatriates2/data/fishback/"

use "~/fishback_dthclim9.dta", clear
qui do "~/fishback_labels.do"

drop if fips == .

*****************
**** WEATHER ****
*****************
preserve
local vars dustbowl dustbowh mhdxw mhdxd mdsxw mdsxd mzdxw mzdxd
keep year stfips ctyfips fips `vars'
sum

collapse (sum) dustbowl dustbowh mhdxw mhdxd mdsxw mdsxd mzdxw mzdxd, by(fips)

qui do "~/fishback_labels.do"

gen extreme_phdi = mhdxd + mhdxw
gen extreme_pdsi = mdsxd + mdsxw
gen extreme_pzma = mzdxd + mzdxw
corr extreme* 

label var dustbowl "(sum) 1930-1940 Dustbowl measure"

label var extreme_phdi "(sum) 1930-1940 months extreme drought or wet, PHDI"
label var extreme_pdsi "(sum) 1930-1940 months extreme drought or wet, PDSI"
label var extreme_pzma "(sum) 1930-1940 months extreme drought or wet, PZMA"

drop m* dustbowh extreme_pzma extreme_pdsi

order fips dust extreme*

sum
desc

tempfile edno
save `edno'
restore

**********************
**** FARM CAPITAL ****
**********************
preserve
local vars fauto fmauto ftruck fmtruk ftract fmtrac farms avesz frmpop
keep year stfips ctyfips fips `vars'
sum

keep if year == 1930 | year == 1940
bysort year: sum 

reshape wide `vars', i(fips) j(year)
sum

foreach i of local vars {
	gen d`i' = log(`i'1940 + 1) - log(`i'1930 + 1)
	}
	
keep fips d* 
order fips d*

sum
desc

label var dfauto "change log # autos on farms '40-'30"
label var dfmauto "change log # farms w/ autos '40-'30" 
label var dftruck "change log # trucks on farms '40-'30"
label var dfmtruk "change log # farms w/ trucks '40-'30" 
label var dftract "change log # tractors on farms '40-'30"
label var dfmtrac "change log # farms w/ tractors '40-'30" 
label var dfarms "change log # farms '40-'30"
label var dfrmpop "change log farm population, '40-'30"
label var davesz "change log average farm size, '40-'30"

tempfile dve
save `dve', replace
restore

*************
*************
*************

preserve
keep if year == 1930

replace ocott30 = (ocott30 / pop30) * 100
gen pctnonwhite = (popnon / pop30) * 100
gen pctwhite = (popw / pop30) * 100
gen pctyoung = pp0509 +pp1014 +pp1519 +pp2024 +pp2529 +pp3034
rename pctneg pctblk

label var ocott30 "# of people reporting cotton textiles manufacturing as occupation as % of pop, 1930"
label var pctyoung "% of pop aged 5-34"
label var pctwhite "% white pop"
label var pctnonwhite "% non-white pop"

keep fips ocott30 memppop pctyoung popdens rptotc26 imr pctwhit //area30 //  pctnonwh demmean pcturb pctfrm pctill pctblk 

sum
desc

tempfile tri
save `tri', replace
restore


*************************************
**** NEW DEAL SPENDING AND OTHER ****
*************************************

*total grants (ndexp): ndexp= fera+cwa+wpa+pubass+pwaf+pwanf2+pra+pba+aaa+fsarr2+ushah;
*total loans (loan): loan= pwanf1+fca+rea+rfc+holc+ushalc;
*total relief grants (relief): relief = fera+cwa+wpa+pubass;
* i.e., ndexp = loan + relief (almost)

preserve
import excel "~/nddist04.xls", sheet("nddist04") firstrow clear
qui do "~/fishback_labels.do"

keep stname state name ndcode rldf3329 ndexp mean9628 pctill30 pctblk30 pctfrmln pcturb3 pubwor aaa loan relief

replace ndexp = log(ndexp)
replace pubwor = log(pubwor + 1)
replace aaa = log(aaa + 1)
replace relief = log(relief + 1)
replace loan = log(loan + 1)

* FIPS CODES *
replace ndcode = ndcode / 10
drop if mod(ndcode, 1) != 0 // 1 obs in Wyoming

rename state state_icpsr
merge m:1 state_icpsr using "~/StateFIPSicsprAB", nogen keep(1 3) keepusing(state_fips)
rename (state_fips ndcode) (stfips ctyfips)
drop state_icpsr //stname name
replace name = proper(name)
order  stfips ctyfips

tostring stfips ctyfips, replace
replace stfips =  "0" + stfips if strlen(stfips) == 1
assert strlen(stfips) == 2

replace ctyfips = "0" + ctyfips if strlen(ctyfips) == 2
replace ctyfips = "00" + ctyfips if strlen(ctyfips) == 1
assert strlen(ctyfips) == 3

gen fips = stfips + ctyfips, after(ctyfips)
destring *fips, replace

drop stfips ctyfips
/*				*/

tempfile cntynames
save `cntynames'

sum

collapse ndexp mean9628 pctill30 pctblk30 pctfrmln pcturb3 aaa pubwor loan relief, by(fips) // a few counties have multiple entries. only affects oklahoma

label var ndexp "total Grants"
label var mean9628 "mean % voting democrat, 1896-1928"
label var pctill30 "% illiterate, int. 30-40"
label var pctblk30 "% black in population, 1930"
label var pctfrmln "% of land on farms, int. 29-39"
label var pcturb3 "% urban, int. 30-40" 
label var aaa "Agric Adjust Admin grants"
label var pubwor "public works grants"
label var loan "total loans"
label var relief "total relief grants"

tempfile chet
save `chet', replace
restore

************************
***** COUNTY NAMES *****
************************
preserve
use `cntynames', clear
keep stname name fips
rename stname state
merge m:1 state using "~/state_dict.dta", keep(1 3)
assert _merge == 3
drop _merge
replace name = name + ", " + state_abbr
keep fips name
rename fips county

tempfile shest
save `shest', replace
restore

**************
**** AREA ****
**************
preserve
keep if year == 1933

keep fips area30

sum
desc

tempfile pet
save `pet', replace
restore

**********************
**** RETAIL SALES ****
**********************
clear
import excel "~/retailsales_data.xls", sheet("ndbhrt05") firstrow clear
keep STATE NDMTCODE RLDF3933 RLDF3929 RLDF3329 RLDF3529
rename *, lower

rename ndmt ndcode

* FIPS CODES *
replace ndcode = ndcode / 10
drop if mod(ndcode, 1) != 0 // 1 obs in Wyoming

rename state state_icpsr
merge m:1 state_icpsr using "~/StateFIPSicsprAB", nogen keep(1 3) keepusing(state_fips)
rename (state_fips ndcode) (stfips ctyfips)
drop state_icpsr //stname name
order  stfips ctyfips

tostring stfips ctyfips, replace
replace stfips =  "0" + stfips if strlen(stfips) == 1
assert strlen(stfips) == 2

replace ctyfips = "0" + ctyfips if strlen(ctyfips) == 2
replace ctyfips = "00" + ctyfips if strlen(ctyfips) == 1
assert strlen(ctyfips) == 3

gen fips = stfips + ctyfips, after(ctyfips)
destring *fips, replace

drop stfips ctyfips

collapse rldf*, by(fips)

* RLDF3933	Growth rate in retail sales per capita, 1933 to 1939
* RLDF3929	Growth rate in retail sales per capita, 1929 to 1939
* RLDF3329	Growth rate in retail sales per capita, 1929 to 1933
* RLDF3529	Growth rate in retail sales per capita, 1929 to 1939

label var rldf3329 "Growth rt sales per cap(1967$),29-33"
label var rldf3933 "Growth rt sales per cap(1967$),33-39"
tempfile shes
save `shes'

*************************************
*************************************
*************************************

clear
use `edno'
merge 1:1 fips using `dve', nogen 
merge 1:1 fips using `tri', nogen 
merge 1:1 fips using `chet', nogen 
merge 1:1 fips using `pet', nogen 
merge 1:1 fips using `shes', nogen 

rename (mean9628 rptotc memppop ocott30) (pctdem pctchurch pctmanuf pctcott)
*rename (dustbowl extreme) (weather_dustb weather_extr)

order fips area popdens ndexp rldf* dustbowl extreme pct* df*

sum
compress



*****************
*****************
*****************

* MAY 12, 2020
keep fips ndexp rldf* dustbowl extreme_phdi pctdem area df* dave aaa pubwor loan relief
rename fips county

* CONVERT TO 1910 COUNTY BOUNDARIES
* Arizona
replace county = 4009 if county == 4011

* Louisiana
replace county = 22097 if county == 22039
replace county = 22019 if county == 22011 | county == 22003 | county == 22053

* New Mexico
replace county = 35017 if county == 35023
replace county = 35019 if county == 35011
replace county = 35005 if county == 35025

* Nevada
replace county = 32013 if county == 32027
replace county = 32009 if county == 32021

* Oklahoma
replace county = 40031 if county == 40033

* Oregon
replace county = 41013 if county == 41031 | county == 41017

* Texas
replace county = 48141 if county == 48229 | county == 48109
replace county = 48137 if county == 48385
replace county = 48355 if county == 48273 | county == 42849
replace county = 48061 if county == 48489
replace county = 48427 if county == 48407

* Utah
replace county = 49047 if county == 49009
replace county = 49051 if county == 49013

collapse (sum) ndexp dustbowl extreme_phdi area aaa pubwor loan relief (mean) rldf* pctdem df* dave, by(county)
sum 

merge 1:1 county using `shest', nogen 

label var dfauto "change log # autos on farms '40-'30"
label var dfmauto "change log # farms w/ autos '40-'30" 
label var dftruck "change log # trucks on farms '40-'30"
label var dfmtruk "change log # farms w/ trucks '40-'30" 
label var dftract "change log # tractors on farms '40-'30"
label var dfmtrac "change log # farms w/ tractors '40-'30" 
label var dfarms "change log # farms '40-'30"
label var dfrmpop "change log farm population, '40-'30"
label var davesz "change log average farm size, '40-'30"

order county name 

save "~/fishback.dta", replace
